<?php
/*逻辑班，模型*/
class Logic_class_model extends CI_Model
{
    public $table = 'logic_class';

    public function __construct()
    {
        parent::__construct();
    }

    /**
     * 返回数据表总条目个数
     * @return [type] [description]
     */
    public function count_all()
    {
        return $this->db->count_all($this->table);
    }

    /**
     * 得到某个学校的逻辑班的数量
     * @return [type] [description]
     */
    public function count_by_school_id($school_id)
    {
        $sql = 'SELECT count(*) as count FROM logic_class WHERE school_id=?';
        $res = $this->db->query($sql, $school_id)->row_array();
        return $res['count'];
    }
    /**
     * 获得所有的条目
     * @return [type] [description]
     */
    public function get_all()
    {
        $res = $this->db->get($this->table)->result_array();
        return $res;
    }

    public function get_all_order()
    {
        $sql = 'SELECT * FROM logic_class where 1 order by name DESC';
        return $this->db->query($sql)->result_array();
    }
    /**
     * 通过id返回条目
     * @param  [type] $id [description]
     * @return [type]     [description]
     */

    public function get_all_order_by_id($school_id)
    {
        $sql = 'SELECT * FROM logic_class where school_id = ?';
        return $this->db->query($sql, $school_id)->result_array();
    }


    public function get_by_id($id)
    {
        return $this->db->get_where($this->table, array('id' => $id))->row_array();
    }
    /**
     * 通过school_id返回条目
     * @param  [type] $id [description]
     * @return [type]     [description]
     */
    public function get_by_school_id($school_id)
    {
        return $this->db->get_where($this->table, array('school_id' => $school_id))->result_array();
    }
    //通过logic_class_number和school_id获取项目
    public function get_by_logic_class_number_and_school_id($logic_class_name, $school_id)
    {
        return $this->db->get_where($this->table, array('name' => $logic_class_name, 'school_id' => $school_id))->row_array();
    }
    //获取学生的待选授课班


    public function get_logic_classes_for_choose($student_id, $school_id, $today)
    {
        $sql = 'SELECT lc.id,lc.name,lc.teacher_id,lc.type,bl.name as big_lecture_name, t.name as teacher_name, t.teacher_number as teacher_number  FROM logic_class as lc,big_lecture as bl,teacher as t where lc.teacher_id=t.id and lc.big_lecture_id=bl.id and start_date<=? and end_date>=? and lc.school_id=? and lc.id not in (SELECT logic_class_id from student_in_logic_class where student_id=?)';
        return $this->db->query($sql, array($today, $today, $school_id, $student_id))->result_array();
    }
    /**
     * 通过type返回条目
     * @param  [type] $type [description]
     * @return [type]       [description]
     */
    public function get_by_type($type)
    {
        return $this->db->get_where($this->table, array('type' => $type))->result_array();
    }
    /**
     * 通过id得到逻辑班信息，包括课程名称
     * @return [type] [description]
     */
    public function get_logic_class_by_id($logic_class_id)
    {
        $sql = 'SELECT lc.id,lc.name,lc.teacher_id,lc.type,lc.big_lecture_id,bl.name as big_lecture_name FROM logic_class as lc,big_lecture as bl WHERE lc.big_lecture_id=bl.id and lc.id=?';
        return $this->db->query($sql, $logic_class_id)->row_array();
    }

    /**
     * 添加一个条目
     * @param [type] $item [description]
     */
    public function add($item)
    {
        return $this->db->insert($this->table, $item);
    }

    /**
     * 删除一个条目
     * @param  [type] $id [description]
     * @return [type]     [description]
     */
    public function delete($id)
    {
        return $this->db->delete($this->table, array('id' => $id));
    }
    //删除一个逻辑班的选课信息
    public function delete_student_in_logic_class($logic_class_id)
    {
        $sql = 'DELETE FROM student_in_logic_class where logic_class_id=?';
        if (!$this->db->query($sql, $logic_class_id)) {
            return false;
        }
        return TURE;
    }

    /**
     * 更新一个条目
     * @param  [type] $id   [description]
     * @param  [type] $item [description]
     * @return [type]       [description]
     */
    public function update($id, $item)
    {
        $this->db->where('id', $id);
        return $this->db->update($this->table, $item);
    }
    /**
     * 通过老师id得到相应的逻辑班
     * @return [type] [description]
     */
    public function get_logic_classes_by_teacher_id($teacher_id)
    {
        $sql = 'SELECT lc.id,lc.name,lc.teacher_id,lc.type,lc.start_date,lc.end_date,bl.name as big_lecture_name FROM logic_class as lc,big_lecture as bl where lc.big_lecture_id=bl.id and lc.teacher_id=?';
        return $this->db->query($sql, $teacher_id)->result_array();
    }
    /**
     * 查看数据库中是否已经有这个逻辑班名称
     * @param  [type] $logic_class_number [description]
     * @return [type]                 [description]
     */
    public function logic_class_name_exist($logic_class_name, $school_id)
    {
        $sql = 'SELECT count(*) as count FROM logic_class as lc WHERE lc.name=? and lc.school_id=?';
        $row = $this->db->query($sql, array($logic_class_name, $school_id))->row_array();
        return ($row['count'] > 0);
    }
    /**
     * 通过教师id,得到逻辑班的所有信息，包括逻辑班号，名称，老师
     * @return [type] [description]
     */
    public function get_all_logic_classes_by_teacher_id($teacher_id)
    {
        $sql = "SELECT lc.id,lc.name,bl.name as big_lecture_name,t.name as teacher_name,lc.type FROM logic_class as lc,teacher as t,big_lecture as bl WHERE lc.big_lecture_id=bl.id and lc.teacher_id=t.id and lc.teacher_id=?";
        return $this->db->query($sql, $teacher_id)->result_array();
    }
    /**
     * 根据逻辑班的id查询相关的知识点
     * @param  [type] $logic_class_id [description]
     * @return [type]                 [description]
     */
    public function get_relative_student_major($logic_class_id)
    {
        $sql = 'SELECT DISTINCT m.id,m.name FROM student_in_logic_class as slc,student as s,major as m WHERE slc.student_id=s.id and s.major_id=m.id and slc.logic_class_id=?';
        return $this->db->query($sql, $logic_class_id)->result_array();
    }
    /**
     * 检查一个学生是否属于一个逻辑班
     * @param  [type] $student_id     [description]
     * @param  [type] $logic_class_id [description]
     * @return [type]                 [description]
     */
    public function check_student_in_logic_class($student_id, $logic_class_id)
    {
        return $this->db->get_where('student_in_logic_class', array('student_id' => $student_id, 'logic_class_id' => $logic_class_id))->row_array();
    }
    /**
     * 检查一个逻辑班中是否有这门课
     * @param  [type] $module_id      [description]
     * @param  [type] $logic_class_id [description]
     * @return [type]                 [description]
     */
    public function check_module_in_logic_class($module_id, $logic_class_id)
    {
        $this->load->model('module_model');
        $module = $this->module_model->get_by_id($module_id);
        if (!$module) {
            return false;
        }
        $big_lecture_id1 = 0;
        if ($module['type'] == 0) {
            $big_lecture_id1 = $module['big_lecture_id'];
        }
        if ($module['type'] == 1) {
            $module = $this->module_model->get_by_id($module['parent_id']);
            $big_lecture_id1 = $module['big_lecture_id'];
        }

        $sql = 'SELECT lc.big_lecture_id FROM logic_class as lc,big_lecture as bl where lc.big_lecture_id=bl.id and lc.id=?';
        $res = $this->db->query($sql, $logic_class_id)->row_array();
        $big_lecture_id2 = $res['big_lecture_id'];
        return ($big_lecture_id1 == $big_lecture_id2);
    }

    /**
     * 查看数据库中是否已经有这个逻辑班号
     * @param  [type] $logic_class_number [description]
     * @return [type]                 [description]
     */
    public function logic_class_number_exist($logic_class_name)
    {
        $sql = 'SELECT count(*) as count FROM logic_class as lc WHERE lc.name=?';
        $row = $this->db->query($sql, $logic_class_name)->row_array();
        return ($row['count'] > 0);
    }

    /**
     * 检查班级名称是否已经存在
     * @return [type] [description]
     */
    public function check_class_name_exist_in_a_school($logic_class_name, $school_id = 7)
    {
        $sql = 'SELECT count(*) as count FROM logic_class AS lc WHERE lc.name=? and lc.school_id=?';
        $row = $this->db->query($sql, array($logic_class_name, $school_id))->row_array();
        return ($row['count'] > 0);
    }

    /**
     * 学生与逻辑班的关系
     */
    public function AddStudentByLogicClass($item)
    {


        // return $this->db->insert('student_in_logic_class', array('student_id' => $student_id, 'logic_class_id' => $logic_class_id))->result_array();
        return $this->db->insert('student_in_logic_class', $item);
    }

    /**
     * 通过逻辑班名称查询逻辑班
     */
    public function get_by_logic_class_name($logic_class_name)
    {
        $sql = 'SELECT lc.id FROM logic_class AS lc WHERE lc.`name` =?';
        return $this->db->query($sql, $logic_class_name)->row_array();
    }

    /**
     * 通过学生ID查询所在逻辑班
     */
    public function GetLogicClassNameByStudentID($student_id)
    {
        $sql = 'SELECT
		lc.`name` AS logic_class_name
	FROM
		student AS s,
		logic_class AS lc,
		student_in_logic_class AS slc
	WHERE
		s.id = slc.student_id
	AND lc.id = slc.logic_class_id
	AND s.student_id = ?';
        return $this->db->query($sql, $student_id)->row_array();
    }
}
